<?php

  //connect to database
  @connectToDb($dbServer, $dbAll, $dbAllPW, $dbName);

  function setItemsReserve($o_products)
  {
    $query = "LOCK TABLES item WRITE";
    mysql_query($query);

    for($i=0; $i < sizeof($o_products);$i+=2) 
    {
      $sql = "SELECT COUNT(*) FROM item WHERE prod_no = " . $o_products[$i] . " AND item_status = 'A'";
      $sql_result = @mysql_query($sql);
      $row = mysql_fetch_array($sql_result);

      if($row[0] < $o_products[$i+1])
      {
        return "0";
	  }
    }
 
    $l = 0;

    for($i=0; $i < sizeof($o_products); $i+=2)
    {
      $item = "SELECT item_no FROM item WHERE prod_no = " . $o_products[$i] . " AND item_status = 'A'";
      $item_result = @mysql_query($item);

      $j = 0;

      while ($row = mysql_fetch_array($item_result))
      {
        $p_item[$j] = $row[0];
        $j++;
      }

      for($k=0; $k < $o_products[$i+1]; $k++)
      {
        $sql = "UPDATE item SET item_status = 'R' WHERE prod_no = " . $o_products[$i] . " AND item_no = " . $p_item[$k];
        mysql_query($sql);
        $order[$l] = $o_products[$i];
		$l++;
        $order[$l] = $p_item[$k];
		$l++;
      }
    }

    $query = "UNLOCK TABLES";
    mysql_query($query);

    return $order;

  }

  function addOrderDetailsDB($c_no, $productItems, $o_freight, $o_merchanise, $o_delivery, $o_insurance, $o_total, $o_card)
  {
    date_default_timezone_set("Australia/Sydney");

    $tDate = date("Y-m-d");

    $query = "LOCK TABLES product_order WRITE, product_orderItem WRITE";
    mysql_query($query);

    $id = "SELECT COUNT(*) FROM product_order";
    $id_result = @mysql_query($id);
    $row = mysql_fetch_array($id_result);

	$id = $row[0] + 1;

    $sql = "INSERT INTO product_order VALUES(" . $id . ", '" . $tDate . "', " . $c_no . ", '" . $o_freight . "', " . $o_merchanise . ", " . $o_delivery . ", " . $o_insurance . ", " . $o_total . ", '" . $o_card[0] . "', '" . $o_card[2] . $o_card[3] . $o_card[4] . $o_card[5] . "', '" . $o_card[1] . "', '" . $o_card[6] . $o_card[7] . "')";

    if(mysql_query($sql))
    {
    }
    else
    {
      echo mysql_error();
      return false;
    }

    for($i=0; $i < sizeof($productItems);$i+=2) 
    {
      $sql = "INSERT INTO product_orderItem VALUES(" . $id . ", " . $productItems[$i] . ", " . $productItems[$i+1] . ")";
      if(mysql_query($sql))
      {}
      else
      {
        echo $sql . " ";
        echo mysql_error();
        return false;
      }
    }

    $query = "UNLOCK TABLES";
    mysql_query($query);

    $_SESSION["orderID"] = $id;

	$sql2 = "UPDATE customer SET financial_status = 'N' WHERE customer_no = " . $c_no;

    if(mysql_query($sql2))
    {
    }
    else
    {
      echo mysql_error();
      return false;
    }

    return true;
  }

  function cancelOrderDB($order_no, $c_no)
  {
    $sql = "DELETE FROM product_orderItem WHERE order_no = ".$order_no;
    if(mysql_query($sql))
    {
      $sql = "DELETE FROM product_order WHERE order_no = ".$order_no;
      if(mysql_query($sql))
      {
		$sql2 = "UPDATE customer SET finanical_status = 'Y' WHERE customer_no = " . $c_no;
		if(mysql_query($sql2))
        {
          return true;
		}
		else
          return false;
      }
      else
      {
        return false;
      }
    }
    else
    {
        return false;
    }
  }

  function getOrderDetailsAll($custID)
  {
    $result = mysql_query("SELECT product_order.order_no, product_order.order_date, item.item_status,
      product_orderItem.prod_no, product_orderItem.item_no, product.prod_type, product.prod_colour,
      product.prod_size, product.prod_description, item.item_sale_price, product_order.total_price
      FROM product_order, product_orderItem, product, item
      WHERE product_order.order_no = product_orderItem.order_no
      AND product_orderItem.prod_no = product.prod_no
      AND product_orderItem.item_no = item.item_no
      AND product_orderItem.prod_no = item.prod_no
      AND product_order.customer_no = ".$custID)
    or die ('Error: '.mysql_error ());

    $i = 0;
    while($row = mysql_fetch_row($result))
    {
      $data[$i] = $row;
      $i++;
    }
      return $data;
  }

  function getOrderDetailsOrderNo($custID, $order_no)
  {
    $result = mysql_query("SELECT product_order.order_no, product_order.order_date, item.item_status,
      product_orderItem.prod_no, product_orderItem.item_no, product.prod_type, product.prod_colour,
      product.prod_size, product.prod_description, item.item_sale_price, product_order.total_price
      FROM product_order, product_orderItem, product, item
      WHERE product_order.order_no = product_orderItem.order_no
      AND product_orderItem.prod_no = product.prod_no
      AND product_orderItem.item_no = item.item_no
      AND product_orderItem.prod_no = item.prod_no
      AND product_order.customer_no = ".$custID."
      AND product_order.order_no = ".$order_no)
    or die ('Error: '.mysql_error ());

    $i = 0;
    while($row = mysql_fetch_row($result))
    {
      $data[$i] = $row;
      $i++;
    }
      return $data;
  }

  function getOrderDetailsOrderDate($custID, $order_date)
  {
    $result = mysql_query("SELECT product_order.order_no, product_order.order_date, item.item_status,
      product_orderItem.prod_no, product_orderItem.item_no, product.prod_type, product.prod_colour,
      product.prod_size, product.prod_description, item.item_sale_price, product_order.total_price
      FROM product_order, product_orderItem, product, item
      WHERE product_order.order_no = product_orderItem.order_no
      AND product_orderItem.prod_no = product.prod_no
      AND product_orderItem.item_no = item.item_no
      AND product_orderItem.prod_no = item.prod_no
      AND product_order.customer_no = ".$custID."
      AND product_order.order_date = '".$order_date."'")
    or die ('Error: '.mysql_error ());

    $i = 0;
    while($row = mysql_fetch_row($result))
    {
      $data[$i] = $row;
      $i++;
    }
      return $data;
  }

  function getOrderDetailsProdNo($custID, $prod_no)
  {
    $result = mysql_query("SELECT product_order.order_no, product_order.order_date, item.item_status,
      product_orderItem.prod_no, product_orderItem.item_no, product.prod_type, product.prod_colour,
      product.prod_size, product.prod_description, item.item_sale_price, product_order.total_price
      FROM product_order, product_orderItem, product, item
      WHERE product_order.order_no = product_orderItem.order_no
      AND product_orderItem.prod_no = product.prod_no
      AND product_orderItem.item_no = item.item_no
      AND product_orderItem.prod_no = item.prod_no
      AND product_order.customer_no = ".$custID."
      AND product_orderItem.prod_no = ".$prod_no)
    or die ('Error: '.mysql_error ());

    $i = 0;
    while($row = mysql_fetch_row($result))
    {
      $data[$i] = $row;
      $i++;
    }
      return $data;
  }

  function checkCustomerDetailsCust($surname, $given_name, $dob, $address)
  {
    $sql = "SELECT * FROM customer WHERE surname = '".$surname."' AND given_name = '".$given_name."' AND date_of_birth = '".$dob."' AND address = '".$address."'";
    $sql_result = @mysql_query($sql);
    $sql_num = @mysql_numrows($sql_result);

    if($sql_num == 0)
    {
      return true;
    }
    else
    {
      $row = mysql_fetch_row($sql_result)or die ('Error: '.mysql_error ());
      $id = $row[0];
      $_SESSION["customerID"] = $id;
      return false;
    }
  }

?>